﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using DatabaseHelper.Core.DAL.Interfaces;
using DatabaseHelper.Core.Models;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using System.Linq;

namespace DatabaseHelper.Core.DAL
{
    public class SqlServerDAL : BaseDatabaseDAL, ISqlServerDAL
    {

        public override void ConnectTo(string connectionString)
        {
            lock (locker)
            {
                ConnectionString = connectionString;
            }
        }

        public IDbConnection UsingCurrentConnection()
        {

            var connection = new SqlConnection(ConnectionString);

            connection.Open();

            return connection;
        }

        public async Task<List<ColumnViewEntity>> GetAllColumns()
        {
            string querySql = @"SELECT t.name AS TableName, " +
                    "s.name AS SchemaName, " +
                    "c.name AS ColumnName, " +
                    "c.column_id AS OrdinalPosition, " +
                    "c.is_nullable AS IsNullable, " +
                    "c.is_identity AS IsIdentity, " +
                    "CASE WHEN EXISTS(SELECT 1 FROM [sys].[all_objects] WHERE type = N'PK' AND parent_object_id = c.object_id AND name IN(SELECT name FROM [sys].[sysindexes] WHERE indid IN(SELECT indid FROM [sys].[sysindexkeys] WHERE id = c.object_id AND colid = c.column_id))) THEN 1 ELSE 0 END AS IsPrimaryKey, " +
                    "c.is_rowguidcol AS IsRowGuidCol, " +
                    "isc.DATA_TYPE AS DataType, " +
                    "isc.CHARACTER_MAXIMUM_LENGTH AS CharacterMaximumLength, " +
                    "c.collation_name AS CollationSchema, " +
                    "ep.value AS ColumnDescription, " +
                    "isc.NUMERIC_PRECISION AS NumericPrecision, " +
                    "isc.NUMERIC_PRECISION_RADIX AS NumericPrecisionRadix, " +
                    "isc.NUMERIC_SCALE AS NumericScale, " +
                    "isc.DATETIME_PRECISION AS DateTimePrecision " +
                    "FROM [sys].[tables] t " +
                    "LEFT JOIN [sys].[all_columns] c ON t.object_id = c.object_id " +
                    "LEFT JOIN [sys].[extended_properties] ep ON c.object_id = ep.major_id AND ep.name = N'MS_Description' AND c.column_id = ep.minor_id " +
                    "LEFT JOIN [sys].[schemas] s ON t.schema_id = s.schema_id " +
                    "LEFT JOIN [sys].[systypes] st ON c.user_type_id = st.xusertype " +
                    "LEFT JOIN INFORMATION_SCHEMA.COLUMNS isc ON t.name=isc.TABLE_NAME AND c.name=isc.COLUMN_NAME ";
            using var connection = UsingCurrentConnection();
            var columns = await connection.QueryAsync<ColumnViewEntity>(querySql);
            return columns.ToList();
        }

        public async Task<string> GetDatabaseName()
        {
            var sql = @"SELECT TOP 1 TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES";
            using var connection = UsingCurrentConnection();
            var databaseName = await connection.QueryFirstAsync<string>(sql);
            return databaseName;
        }
    }
}
